﻿using System;
using System.Collections;
using System.ComponentModel;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlServerCe;
using System.Runtime.InteropServices;
namespace HSKCards
{
    public partial class HSKCards : Form
    {
        // database variables
        System.Data.SqlServerCe.SqlCeConnection conn;
        System.Data.SqlServerCe.SqlCeCommand comm;
        System.Data.SqlServerCe.SqlCeDataReader result;

        // Stores one entry from the database.  One variable for each column
        int ID;
        string traditional;
        string simplified;
        string pinyin;
        string english;
        string pos;
        int level;
        bool studylist;
        bool known;
        bool amend;
        short viewcount;
        DateTime lastview;

        // Integer to keep count number of known (memorized) words
        int knownwordcount;
        int totalwordcount;
        
        // Flags to track buttons
        bool p;
        bool trad;

        int previousTrackBar;

        // Stack to enable undo support
        Stack previousstack;

        // Integer to track number of entries in study list
        int studylistcount;

        
        [DllImport("coredll.dll")]
        static extern int AddFontResource(string lpszFilename);
        [DllImport("coredll.dll")]
        static extern bool RemoveFontResource(string lpFileName);
        
        // String to track current path
        string programpath;

        public HSKCards()
        {            
            InitializeComponent();

            programpath = System.IO.Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().GetName().CodeBase);
            
            //set fonts of UI elements.  MingLiU for chinese support. MingLiU for Latin extended B
            AddFontResource(programpath + "\\mingliu.ttf");
            AddFontResource(programpath + "\\arial.ttf");

            front.Font = new Font("PMingLiUCustom", 36, FontStyle.Regular);
            textBoxPinyin.Font = new Font("Arial", 20, FontStyle.Regular);
            textBoxEnglish.Font = new Font("Tahoma", 12, FontStyle.Regular);
            tradbutton.Font = new Font("PMingLiUCustom", 12, FontStyle.Regular);
            
            // frequently used variables
            conn = new System.Data.SqlServerCe.SqlCeConnection("Data Source='" + programpath + "\\HSK.sdf'; Max Buffer Size=2048;");
            conn.Open();

            Init2();
            
        }

        private void Init2()
        {

            previousstack = new Stack(10);

            // Set defaults
            comm = new System.Data.SqlServerCe.SqlCeCommand();
            comm.Connection = conn;
            comm.CommandText = "SELECT collection FROM Settings where id = 0";
            result = comm.ExecuteReader();
            result.Read();
            previousTrackBar = trackBar1.Value = result.GetInt16(0);
            comm.Dispose();
            result.Close();

            // First undo entry
            ID = 1;

            trackBar1_ValueChanged(new object(), new EventArgs());
        }


        private void SetTitleBarCounter()
        {

            // Get number of known words
            comm = new System.Data.SqlServerCe.SqlCeCommand();
            comm.Connection = conn;
            comm.CommandText = "SELECT COUNT(known) FROM entries WHERE (known = 1 and collection = " + trackBar1.Value.ToString() + ")";
            result = comm.ExecuteReader();
            result.Read();
            knownwordcount = result.GetInt32(0);
            comm.Dispose();
            result.Close();


            // Get total number of words
            comm = new System.Data.SqlServerCe.SqlCeCommand();
            comm.Connection = conn;
            comm.CommandText = "SELECT COUNT(known) FROM entries WHERE (collection = " + trackBar1.Value.ToString() + ")";
            result = comm.ExecuteReader();
            result.Read();
            totalwordcount = result.GetInt32(0);
            comm.Dispose();
            result.Close();

            this.Text = "#" + trackBar1.Value.ToString() + " - " + knownwordcount.ToString() + "/" + totalwordcount.ToString();
        }

        // Two steps to generate entry.  First get a valid random number.
        private void generate_New_Entry()
        {            
            generateID();
            getandDisplayEntry();
        }

        private void generateID()
        {
            // push current value to stack for undo support
            previousstack.Push(ID);       
            if (previous.Enabled == false) previous.Enabled = true;

            // study list mode on. Select a random card from study list
            if (plistbutton.BackColor == SystemColors.Highlight)
            {
                comm = new System.Data.SqlServerCe.SqlCeCommand();
                comm.Connection = conn;
                
                comm.CommandText = "SELECT  TOP (1) ID from entries where studylist = 1 ";
                comm.CommandText += " and collection = " + trackBar1.Value.ToString();
                if (shuffleMenuItem.Checked) comm.CommandText += " ORDER BY NEWID()";
                else comm.CommandText += " Order By lastview asc ";
                result = comm.ExecuteReader();
                result.Read();
                ID = result.GetInt32(0);
                comm.Dispose();
                result.Close();
            }

            // generate card based on selected levels
            else
            {
                comm = new System.Data.SqlServerCe.SqlCeCommand();
                comm.Connection = conn;
                StringBuilder temp = new StringBuilder("SELECT  TOP (1) ID FROM entries WHERE ");
                temp.Append(" collection = " + trackBar1.Value.ToString());
                if (hideKnownMenuItem.Checked) temp.Append(" and known = 0 ");

                if (shuffleMenuItem.Checked) temp.Append(" ORDER BY NEWID()");
                else temp.Append(" Order By lastview asc ");
                comm.CommandText = temp.ToString();
                result = comm.ExecuteReader();
                result.Read();
                ID = result.GetInt32(0);
                comm.Dispose();
                result.Close();
            }
        }  

        private void getandDisplayEntry()
        {
            // get entry from Database
            comm = new System.Data.SqlServerCe.SqlCeCommand();
            comm.Connection = conn;
            comm.CommandText = "Select * from entries where ID = " + ID.ToString();
            result = comm.ExecuteReader();
            //while (result.Read())
            result.Read();
            
            traditional = result.GetString(1);
            simplified = result.GetString(2);
            pinyin = result.GetString(3);
            english = result.GetString(4);
            pos = result.GetString(5);
            level = result.GetInt32(6);
            studylist = result.GetBoolean(7);
            known = result.GetBoolean(8);
            amend = result.GetBoolean(9);
            viewcount = result.GetInt16(10);
            lastview = result.GetDateTime(11);

            result.Close();            
            comm.Dispose();

            comm = new System.Data.SqlServerCe.SqlCeCommand();
            comm.Connection = conn;
            comm.CommandText = "UPDATE entries SET viewcount =" + viewcount++.ToString()+ ", lastview ='"+ DateTime.Now.ToShortDateString() + " " + DateTime.Now.ToShortTimeString() + "' WHERE (ID = " + ID.ToString() + ")";
            comm.ExecuteNonQuery();
            comm.Dispose();
            
            // If word is not in study list. enable "add to list" button
            if (studylist == false)
            {
                addToList.Text = "";
                addToList.BackColor = SystemColors.Control;
            }
            else
            {
                addToList.Text = "Study";
                addToList.BackColor = SystemColors.Highlight;
            }

            if (known == false)
            {
                MarkKnown.Text = "";
                MarkKnown.BackColor = SystemColors.Control;
            }
            else
            {
                MarkKnown.Text = "Known";
                MarkKnown.BackColor = SystemColors.Highlight;
            }

            if (amend == false)
            {
                amendButton.Text = "";
                amendButton.BackColor = SystemColors.Control;
            }
            else
            {
                amendButton.Text = "Amend";
                amendButton.BackColor = SystemColors.Highlight;
            }
            

            // Display the entry on the front of the flash card.  Hide back of card.
            if (trad) { front.Text = traditional; }
            else { front.Text = simplified; }
            clearText();

            // To do: code to display HSK level of word
        }

        private void front_Click(object sender, EventArgs e)
        {
            generate_New_Entry();
        }

        private void back_GetFocus(object sender, EventArgs e)
        {
            setText();
            front.Focus();        
        }

        private void clearText()
        {
            textBoxEnglish.Text = "";
            textBoxPinyin.Text = "";
        }
        private void setText()
        {
            textBoxPinyin.Text = pinyin;

            textBoxEnglish.Text = "";
            if (pos.Length > 0) textBoxEnglish.Text += pos + ": ";
            textBoxEnglish.Text += english;

            //textBoxLevel.Text = "Level: " + level.ToString();
        }

        private void tradbutton_Click(object sender, EventArgs e)
        {
            if (trad)
            {
                tradbutton.Text = "简体";
                front.Text = simplified;
                trad = false;
            }
            else
            {
                tradbutton.Text = "繁體";
                front.Text = traditional;
                trad = true;
            }
        }

        private void previous_Click(object sender, EventArgs e)
        {
            ID = (int)previousstack.Pop();
            getandDisplayEntry();
            if (previousstack.Count == 0) previous.Enabled = false;
        }

        private void plistbutton_Click(object sender, EventArgs e)
        {
            if (plistbutton.BackColor == SystemColors.Control)
            {
                plistbutton.BackColor = SystemColors.Highlight;
                p = true;
                // disable trackbar
            }
            else 
            {
                plistbutton.BackColor = SystemColors.Control;
                p = false;
                // enable disable trackbar
            }
        }

        private void MarkKnown_Click(object sender, EventArgs e)
        {
            if (MarkKnown.Text == "")
            {
                // mark entry as known
                comm = new System.Data.SqlServerCe.SqlCeCommand();
                comm.Connection = conn;
                comm.CommandText = "UPDATE entries SET known = 1 WHERE (ID = " + ID.ToString() + ")";
                comm.ExecuteNonQuery();
                comm.Dispose();

                // change text
                MarkKnown.Text = "Known";
                MarkKnown.BackColor = SystemColors.Highlight;

                // increment word count, show new total in title bar
                knownwordcount++;
                if (knownwordcount == totalwordcount)
                {
                    hideKnownMenuItem.Checked = false;
                    hideKnownMenuItem.Enabled = false;
                }
                this.Text = "#" + trackBar1.Value.ToString() + " - " + knownwordcount.ToString() + "/" + totalwordcount.ToString();
            }
            else
            {
                // mark entry as studylist false
                comm = new System.Data.SqlServerCe.SqlCeCommand();
                comm.Connection = conn;
                comm.CommandText = "UPDATE entries SET known = 0 WHERE (ID = " + ID.ToString() + ")";
                comm.ExecuteNonQuery();
                comm.Dispose();

                // change addtolist text
                MarkKnown.Text = "";
                MarkKnown.BackColor = SystemColors.Control;

                // if no entries, disable P
                knownwordcount--;
                hideKnownMenuItem.Enabled = true;
                this.Text = "#" + trackBar1.Value.ToString() + " - " + knownwordcount.ToString() + "/" + totalwordcount.ToString(); 
            }
        }

        private void addToList_Click(object sender, EventArgs e)
        {
            if (addToList.Text == "")
            {
                // mark entry as studylist true
                comm = new System.Data.SqlServerCe.SqlCeCommand();
                comm.Connection = conn;
                comm.CommandText = "UPDATE entries SET studylist = 1 WHERE (ID = " + ID.ToString() + ")";
                comm.ExecuteNonQuery();
                comm.Dispose();

                // change addtolist text
                addToList.Text = "Study";
                addToList.BackColor = SystemColors.Highlight;

                // enable p
                studylistcount++;
                plistbutton.Text = studylistcount.ToString();
                plistbutton.Enabled = true;
            }
            else
            {
                // mark entry as studylist false
                comm = new System.Data.SqlServerCe.SqlCeCommand();
                comm.Connection = conn;
                comm.CommandText = "UPDATE entries SET studylist = 0 WHERE (ID = " + ID.ToString() + ")";
                comm.ExecuteNonQuery();
                comm.Dispose();

                // change addtolist text
                addToList.Text = "";
                addToList.BackColor = SystemColors.Control;

                // if no entries, disable P
                studylistcount--;
                plistbutton.Text = studylistcount.ToString();
                if (studylistcount == 0)
                {
                    plistbutton.Enabled = false;
                    plistbutton.BackColor = SystemColors.Control;
                    p = false;
                }
            }
        }


        private void amendButton_Click(object sender, EventArgs e)
        {
            if (amendButton.Text == "")
            {
                // mark entry as amend
                comm = new System.Data.SqlServerCe.SqlCeCommand();
                comm.Connection = conn;
                comm.CommandText = "UPDATE entries SET amend = 1 WHERE (ID = " + ID.ToString() + ")";
                comm.ExecuteNonQuery();
                comm.Dispose();

                // change text
                amendButton.Text = "Amend";
                amendButton.BackColor = SystemColors.Highlight;
            }
            else
            {
                // mark entry as studylist false
                comm = new System.Data.SqlServerCe.SqlCeCommand();
                comm.Connection = conn;
                comm.CommandText = "UPDATE entries SET amend = 0 WHERE (ID = " + ID.ToString() + ")";
                comm.ExecuteNonQuery();
                comm.Dispose();

                // change addtolist text
                amendButton.Text = "";
                amendButton.BackColor = SystemColors.Control;
            }
        }

        private void clearStudyListMenuItem_Click(object sender, EventArgs e)
        {
            DialogResult dr = MessageBox.Show("Delete all items from Study List?", "Warning", MessageBoxButtons.YesNo, MessageBoxIcon.Question, MessageBoxDefaultButton.Button2);
            if (dr == DialogResult.Yes)
            {
                Cursor.Current = Cursors.WaitCursor;
                plistbutton.BackColor = SystemColors.Control;
                p = false;
                comm = new System.Data.SqlServerCe.SqlCeCommand();
                comm.Connection = conn;
                comm.CommandText = "update entries set studylist = 0 where collection = " + trackBar1.Value.ToString();
                comm.ExecuteNonQuery();
                comm.Dispose();
                trackBar1_ValueChanged(new Object(), new EventArgs());
                Cursor.Current = Cursors.Default;
            }
        }

        private void clearKnownListMenuItem_Click(object sender, EventArgs e)
        {
            DialogResult dr = MessageBox.Show("Delete all items from Known Words List?", "Warning", MessageBoxButtons.YesNo, MessageBoxIcon.Question, MessageBoxDefaultButton.Button2);
            if (dr == DialogResult.Yes)
            {
                Cursor.Current = Cursors.WaitCursor;
                comm = new System.Data.SqlServerCe.SqlCeCommand();
                comm.Connection = conn;
                comm.CommandText = "update entries set known = 0 where collection = " + trackBar1.Value.ToString();
                comm.ExecuteNonQuery();
                comm.Dispose();
                trackBar1_ValueChanged(new Object(), new EventArgs());
                Cursor.Current = Cursors.Default;
            }
        }

        private void HSKCards_KeyDown(object sender, KeyEventArgs e)
        {
            if ((e.KeyCode == System.Windows.Forms.Keys.Up))
            {
                // Up
            }
            if ((e.KeyCode == System.Windows.Forms.Keys.Down))
            {
                // Down
            }
            if ((e.KeyCode == System.Windows.Forms.Keys.Left))
            {
                if (previous.Enabled == true)
                {
                    ID = (int)previousstack.Pop();
                    getandDisplayEntry();
                    if (previousstack.Count == 0) previous.Enabled = false;
                }
                // Left
            }
            if ((e.KeyCode == System.Windows.Forms.Keys.Right))
            {
                generate_New_Entry();
                // Right
            }
            if ((e.KeyCode == System.Windows.Forms.Keys.Enter))
            {
                setText();
                // Enter
            }
        }

        private void menuItemShowRelated_Click(object sender, EventArgs e)
        {
            Words words = new Words(ID, conn,trad);
            words.ShowDialog();
            words.Close();
        }

        private void trackBar1_ValueChanged(object sender, EventArgs e)
        {
            SetTitleBarCounter();
            saveSettings();
            restoreSettings();
            generate_New_Entry();
        }

        private void restoreSettings()
        {
            //restore settings
            comm = new System.Data.SqlServerCe.SqlCeCommand();
            comm.Connection = conn;
            comm.CommandText = "SELECT * FROM Settings where id = " + trackBar1.Value.ToString();
            result = comm.ExecuteReader();
            result.Read();
            p = result.GetBoolean(1);
            trad = result.GetBoolean(2);
            hideKnownMenuItem.Checked = result.GetBoolean(4);
            shuffleMenuItem.Checked = result.GetBoolean(5);
            hideKnownMenuItem.Enabled = result.GetBoolean(6);
            comm.Dispose();
            result.Close();

            trackBar1.Enabled = true;
            if (p == true)
            {
                plistbutton.BackColor = SystemColors.Highlight;
            }
            else
            {
                plistbutton.BackColor = SystemColors.Control;
            }

            if (trad == true)
            {
                tradbutton.Text = "繁體";
            }
            else
            {
                tradbutton.Text = "简体";
            }

            // If there are entries in studylist, enable P
            comm = new System.Data.SqlServerCe.SqlCeCommand();
            comm.Connection = conn;
            comm.CommandText = "SELECT COUNT(ID) FROM Entries where studylist = 1 and collection = " + trackBar1.Value.ToString();
            result = comm.ExecuteReader();
            result.Read();
            studylistcount = result.GetInt32(0);
            comm.Dispose();
            result.Close();
            plistbutton.Text = studylistcount.ToString();
            if (studylistcount == 0)
            {
                plistbutton.Enabled = false;
            }
            else {
                plistbutton.Enabled = true;
            }
        }

        private void saveSettings()
        {
            //Save settings
            comm = new System.Data.SqlServerCe.SqlCeCommand();
            comm.Connection = conn;
            comm.CommandText = "UPDATE Settings SET p  = " + Convert.ToInt32(p).ToString() +
                ", trad = " + Convert.ToInt32(trad).ToString() +
                ", hideknown = " + Convert.ToInt32(hideKnownMenuItem.Checked).ToString() +
                ", shuffle = " + Convert.ToInt32(shuffleMenuItem.Checked).ToString()  + 
                ", hideknownenabled = " + Convert.ToInt32(hideKnownMenuItem.Enabled).ToString() +
                " WHERE (ID = " + previousTrackBar.ToString() + ")";
            comm.ExecuteNonQuery();
            comm.Dispose();
            comm.Dispose();
            result.Close();

            previousTrackBar = trackBar1.Value;
        }

        private void hideKnownMenuItem_Click(object sender, EventArgs e)
        {

            if (((MenuItem)sender).Checked)
            {
                ((MenuItem)sender).Checked = false;
            }
            else 
            { 
                ((MenuItem)sender).Checked = true; 
            }
        }

        private void HSKCards_Closed(object sender, CancelEventArgs e)
        {
            SqlCeCommand commloop = new System.Data.SqlServerCe.SqlCeCommand();
            commloop.Connection = conn;
            commloop.CommandText = "SELECT collection, COUNT(known) FROM entries WHERE (known = 1) GROUP BY collection";
            SqlCeDataReader resultloop = commloop.ExecuteReader();
            commloop.Dispose();

            SqlCeCommand commif = new System.Data.SqlServerCe.SqlCeCommand();
            commif.Connection = conn;
            commif.CommandText = "SELECT COUNT(l1) FROM Progress WHERE (Date = '" + DateTime.Now.ToShortDateString() + "')";
            SqlCeDataReader resultif = commif.ExecuteReader();
            commif.Dispose();
            resultif.Read();
            if (resultif.GetInt32(0) == 0)
            {


                // add entry to database
                comm = new System.Data.SqlServerCe.SqlCeCommand();
                comm.Connection = conn;
                comm.CommandText = "INSERT INTO Progress (Date, l1, l2, l3, l4, l5, l6, l7, l8, l9) VALUES ('" +
                    DateTime.Now.ToShortDateString() + "'";
                while (resultloop.Read())
                {
                    comm.CommandText += ", " + resultloop.GetInt32(1).ToString();
                }
                comm.CommandText += ")";

                comm.ExecuteNonQuery();
                comm.Dispose();
            }
            else
            {
                // mark entry as studylist true
                comm = new System.Data.SqlServerCe.SqlCeCommand();
                comm.Connection = conn;

                comm.CommandText = "UPDATE Progress SET ";
                while (resultloop.Read())
                {

                    comm.CommandText += "l" + resultloop.GetInt16(0).ToString();
                    comm.CommandText += " = " + resultloop.GetInt32(1).ToString() + ",";
                }
                comm.CommandText = comm.CommandText.Remove(comm.CommandText.Length - 1, 1);
                comm.CommandText += " WHERE (Date = '" + DateTime.Now.ToShortDateString() + "')";

                comm.ExecuteNonQuery();
                comm.Dispose();
            }
            resultif.Close();
            resultloop.Close();


            RemoveFontResource(programpath + "\\mingliu.ttf");
            RemoveFontResource(programpath + "\\arial.ttf");


            comm = new System.Data.SqlServerCe.SqlCeCommand();
            comm.Connection = conn;
            comm.CommandText = "UPDATE Settings SET collection = " + trackBar1.Value.ToString() +
                " WHERE (ID = 0)";
            comm.ExecuteNonQuery();
            comm.Dispose();
            conn.Close();

        }

        private void menuItem4_Click(object sender, EventArgs e)
        {
            if (menuItem4.Text == "Switch to SCTOP")
            {
                conn.Close();
                conn = new System.Data.SqlServerCe.SqlCeConnection("Data Source='" + programpath + "\\SCTOP.sdf'; Max Buffer Size=2048;");
                conn.Open();
                menuItem4.Text = "Switch to HSK";
                Init2();
            }
            else
            {
                conn.Close();
                conn = new System.Data.SqlServerCe.SqlCeConnection("Data Source='" + programpath + "\\HSK.sdf'; Max Buffer Size=2048;");
                conn.Open();
                menuItem4.Text = "Switch to SCTOP";
                Init2();
            }
        }
    }
}